﻿using Microsoft.Office.Interop.Word;
using System.Data.OleDb;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DataAccess;
using System.IO;
namespace ExportToWord
{
    public partial class Form1 : Form
    {
        private Database db;
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var rst = openFileDialog1.ShowDialog(this);
            if (rst == System.Windows.Forms.DialogResult.OK)
            {
                tbDatabase.Text = openFileDialog1.FileName;
                OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
                builder.Provider = "Microsoft.ACE.OLEDB.12.0";
                builder.DataSource = tbDatabase.Text;
                this.db = new Database("db", "System.Data.OleDb", builder.ConnectionString);
                try
                {
                    var dtHospital = this.db.ExecuteDataTable("SELECT DISTINCT [医院] FROM [topic list-总表] WHERE [医院] <>'' AND [医院] IS NOT NULL ORDER BY [医院]");
                    var dtDepartments = this.db.ExecuteDataTable("SELECT DISTINCT [修订后国内分科] FROM [topic list-总表] WHERE [修订后国内分科] <>'' AND [修订后国内分科] IS NOT NULL ORDER BY [修订后国内分科]");

                    clbHospital.DataSource = dtHospital;
                    clbHospital.ValueMember = "医院";
                    clbHospital.DisplayMember = "医院";

                    clbDepartments.DataSource = dtDepartments;
                    clbDepartments.ValueMember = "修订后国内分科";
                    clbDepartments.DisplayMember = "修订后国内分科";
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(tbDatabase.Text))
            {
                MessageBox.Show("请选择数据库", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (clbHospital.CheckedIndices.Count == 0 || clbDepartments.CheckedIndices.Count == 0)
            {
                MessageBox.Show("没有选择任何”医院“和”修订后国内分科“\n\n没有要导出的数据", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            string select = "SELECT [topic id],[修订后国内分科] as [专科],[英文标题],[中文标题],'' as [英文作者],[待译英文字数],[翻译费（RMB）],'' as [翻译专家姓名],'' as [职称],'' as [科室],'' as [Email],'' as [手机] FROM [topic list-总表] WHERE [医院]=@hospital";
            if (!cbDepartments.Checked)
            {
                string[] checkedDepartments = new string[clbDepartments.CheckedIndices.Count];
                for (int i = 0; i < clbDepartments.CheckedIndices.Count; i++)
                {
                    var item = (DataRowView)clbDepartments.Items[clbDepartments.CheckedIndices[i]];
                    checkedDepartments[i] = item[0].ToString().Replace("'", "''");
                }
                select += " AND [修订后国内分科] IN ('" + string.Join("','", checkedDepartments) + "')";
            }
            select += " ORDER BY [修订后国内分科]";
            string path = Path.GetDirectoryName(tbDatabase.Text);
            string filePath = Path.Combine(path, "word");
            if (!Directory.Exists(filePath))
            {
                Directory.CreateDirectory(filePath);
            }
            var wordApp = new Microsoft.Office.Interop.Word.Application();
            for (int i = 0; i < clbHospital.CheckedIndices.Count; i++)
            {
                var item = (DataRowView)clbHospital.Items[clbHospital.CheckedIndices[i]];
                var doc = wordApp.Documents.Add();
                var selection = doc.ActiveWindow.Selection;
                var hospital = item[0].ToString();
                var data = db.ExecuteDataTable(select, new Parameter("hospital", hospital));

                selection.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
                selection.Font.Name = "仿宋";
                selection.Font.NameAscii = "Arial";
                selection.Font.NameOther = "Arial";

                selection.Font.Size = 16;
                selection.TypeText("UpToDate临床顾问中文版");
                selection.TypeParagraph();
                selection.TypeText(hospital + "待译专题及专家列表(" + data.Rows.Count + "篇)");
                selection.TypeParagraph();
                selection.TypeParagraph();
                selection.Font.Size = 10.5F;
                var range = selection.Range;

                var table = selection.Tables.Add(range, data.Rows.Count + 1, data.Columns.Count - 1);
                if (!string.IsNullOrWhiteSpace(cmbTableStyle.Text))
                {
                    try
                    {
                        table.set_Style(cmbTableStyle.Text);
                    }
                    catch (Exception)
                    {
                    }
                }
                //            ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=4, NumColumns:= _
                //    7, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
                //    wdAutoFitFixed
                //With Selection.Tables(1)
                //    If .Style <> "网格型" Then
                //        .Style = "网格型"
                //    End If
                //    .ApplyStyleHeadingRows = True
                //    .ApplyStyleLastRow = False
                //    .ApplyStyleFirstColumn = True
                //    .ApplyStyleLastColumn = False
                //    .ApplyStyleRowBands = True
                //    .ApplyStyleColumnBands = False
                //End With


                int rowIndex = 1;
                for (int j = 1; j < data.Columns.Count; j++)
                {
                    table.Cell(rowIndex, j).Range.Text = data.Columns[j].ColumnName;
                }
                for (int j = 0; j < data.Rows.Count; j++)
                {
                    //contributors
                    for (int k = 1; k < data.Columns.Count; k++)
                    {
                        if (data.Columns[k].ColumnName == "英文作者")
                        {
                            string textFile = Path.Combine(path, "contributors\\" + data.Rows[j]["topic id"].ToString() + ".txt");
                            table.Cell(j + 2, k).Range.Text = GetAuthors(textFile);
                            continue;
                        }
                        table.Cell(j + 2, k).Range.Text = data.Rows[j][k].ToString();
                    }
                }
                string saveFile = Path.Combine(filePath, hospital.Trim().Replace("\t", "") + ".docx");
                //MessageBox.Show("word文件:" + saveFile);
                doc.SaveAs2(saveFile);
                doc.Close();
            }
            wordApp.Quit(false);
            MessageBox.Show("完成！");
        }

        private string GetAuthors(string textFile)
        {
            if (!File.Exists(textFile))
            {
                return string.Empty;
            }
            try
            {
                StreamReader reader = new StreamReader(textFile);
                int line = 0;
                string text = string.Empty;
                while (!reader.EndOfStream)
                {
                    text = reader.ReadLine().Trim();
                    if (line == 0)
                    {
                        if (text != "Authors")
                        {
                            text = string.Empty;
                            break;
                        }
                    }
                    else
                    {
                        if (!string.IsNullOrWhiteSpace(text))
                        {
                            break;
                        }
                    }
                    line++;
                }
                return text;
            }
            catch (Exception)
            {
                return string.Empty;
            }

        }

        private void cbHospital_CheckedChanged(object sender, EventArgs e)
        {
            for (int i = 0; i < clbHospital.Items.Count; i++)
            {
                clbHospital.SetItemChecked(i, cbHospital.Checked);
            }
            clbHospital.Enabled = !cbHospital.Checked;
        }

        private void cbDepartments_CheckedChanged(object sender, EventArgs e)
        {
            for (int i = 0; i < clbDepartments.Items.Count; i++)
            {
                clbDepartments.SetItemChecked(i, cbDepartments.Checked);
            }
            clbDepartments.Enabled = !cbDepartments.Checked;
        }
    }
}
